from DataCompare import DataComparison
import os


if __name__=='__main__':
 # curpath = os.path.dirname(os.path.abspath(__file__))
 # files = os.listdir(curpath)
 # files=list(filter(lambda x : x.endswith('.txt'),files))
 # print(files)
 # sql_data={}
 # for file in files:
 #   with open(file,'r',encoding='utf-8') as f:
 #       sql_data[file.split('.')[0]]=f.read()
 # for key,value in sql_data.items():
 #    print(key)
 #    print(value)
 # 自定义报告
 erp='''
declare @startdate datetime='2024-04-01 00:00:00';
declare @enddate datetime='2024-04-30 23:59:59';

--@CustomerID=1

--新版



SELECT '国际支付宝' AS '速卖通区域',MAX(a.BillDate) '结算时间',a.MerchantOrderNumber '平台订单号',a.OrderSourceID '店铺ID',a.OrderSourceName '店铺',a.Currency '币种',

ISNULL((CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN '订单收入' 

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '全托管供货款%' THEN '订单收入' 

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '全托管供货款%' THEN '订单收入' 

			 WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入' 

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入' 			 

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

	  ELSE '非订单' END),'') AS '单据类型',	

SUM(CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN ISNULL(a.Amount,0) 

	     WHEN a.BillType = '收款' AND a.Remarks LIKE '全托管供货款%' THEN ISNULL(a.Amount,0) 

	     WHEN a.BillType = '扣款' AND a.Remarks LIKE '全托管供货款%' THEN ISNULL(a.Amount,0)  

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN ISNULL(a.Amount,0) 			 

ELSE 0 END) AS '销售收入',	

SUM(CASE WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN ISNULL(a.Amount,0) 

	     WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0) 

ELSE 0 END) AS '佣金',	

SUM(CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN 0

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '全托管供货款%' THEN 0

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN 0

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN 0		

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '联盟佣金退回%' THEN 0

		 WHEN a.BillType = '收款' AND REPLACE(a.Remarks , ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN 0

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '半托管发货物流费%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '全托管供货款%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN 0

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟物流费代扣_代扣_正向配送费%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '半托管发货物流费%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '直通车充值%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'AEP4P回款金充值%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金扣佣%' THEN 0

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金退回%' THEN 0	 

		 WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN 0	 

		 WHEN a.BillType = '分账' AND a.Remarks LIKE '半托管发货物流费%' THEN 0	 

		 WHEN a.BillType = '分账' AND a.Remarks LIKE '联盟佣金扣佣%' THEN 0	 		 

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN 0	  

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN 0	  

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '联盟佣金退回%' THEN 0	 		

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN 0

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN 0

		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN 0

		 WHEN a.BillType = '服务费' AND a.Remarks ='' THEN 0	 

		 WHEN a.BillType = '服务费' AND a.Remarks LIKE '自动转账%' THEN 0	  	 		 		 

ELSE ISNULL(a.Amount,0) END) AS '平台其他费用',

SUM(CASE WHEN a.BillType = '收款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟物流费代扣_代扣_正向配送费%' THEN ISNULL(a.Amount,0) 		

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '分账' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0) 

ELSE 0 END) AS '平台运费',	

SUM(CASE WHEN a.BillType = '扣款' AND a.Remarks LIKE '直通车充值%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'AEP4P回款金充值%' THEN ISNULL(a.Amount,0) 

ELSE 0 END) AS '充值广告费',	

SUM(CASE WHEN a.BillType = '分账' AND a.Remarks LIKE '联盟佣金扣佣%' THEN ISNULL(a.Amount,0)  		

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金扣佣%' THEN ISNULL(a.Amount,0)  	

		 WHEN a.BillType = '收款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0) 

		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0) 

ELSE 0 END) AS '广告费',

SUM(CASE WHEN a.BillType = '服务费' AND a.Remarks ='' THEN ISNULL(a.Amount,0)

		 WHEN a.BillType = '服务费' AND a.Remarks LIKE '自动转账%' THEN ISNULL(a.Amount,0)

ELSE 0 END) AS '财务费用',

ISNULL(t.AliExpressTrusteeType,'非托管') AS '托管类型'

FROM RB_Crawler_AliExpress_BillHistory a WITH(NOLOCK)

LEFT JOIN (

	SELECT a.MerchantOrderNumber,

	MIN(CASE WHEN a.CrawlConfigId <> 0 AND a.Remarks LIKE '半托管%' THEN '半托管' 

	WHEN a.CrawlConfigId = 0 THEN '全托管' 

	ELSE '非托管' END) AliExpressTrusteeType

	FROM RB_Crawler_AliExpress_BillHistory a WITH(NOLOCK) 

	WHERE (a.BillType !='转账' AND a.BillType !='兑换') 

	AND a.BillDate >= @startdate 

	AND a.BillDate < @enddate

	AND ISNULL(MerchantOrderNumber,'') <> ''

	GROUP BY a.MerchantOrderNumber

) AS t ON a.MerchantOrderNumber = t.MerchantOrderNumber

WHERE (a.BillType !='转账' AND a.BillType !='兑换')

AND a.BillDate >= @startdate

AND a.BillDate <= @enddate

--AND a.MerchantOrderNumber='3031820361665770'

-- AND {{ a.OrderSourceID IN (@OrderSourceID) }}

GROUP BY a.MerchantOrderNumber,a.OrderSourceID,a.OrderSourceName,a.Currency,

ISNULL((CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN '订单收入' 

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '全托管供货款%' THEN '订单收入' 

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '全托管供货款%' THEN '订单收入' 

			 WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入' 

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入' 			 

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'

			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'

	  ELSE '非订单' END),''),

	  t.AliExpressTrusteeType



UNION ALL



SELECT '俄罗斯' AS '速卖通区域',MAX(a.BillDate) '结算时间',a.BusinessOrder '平台订单号',a.OrderSourceID '店铺ID',a.OrderSourceName '店铺',a.Currency '币种',

ISNULL((

 CASE WHEN a.BillType = '放款' THEN '订单收入'  

	  WHEN a.BillType = '退款' THEN '订单退款'

	  WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN '订单收入' 		  

	  WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN '订单退款'	  

	  WHEN a.BillType = '垫付追偿' THEN '订单退款'	  

	  ELSE '非订单' END),'') AS '单据类型',	

SUM(

 CASE WHEN a.BillType = '放款' THEN ISNULL(a.AmountOfMoney,0)	

	  WHEN a.BillType = '退款' THEN ISNULL(a.AmountOfMoney,0)	

	  WHEN a.BillType = '垫付追偿' THEN ISNULL(a.AmountOfMoney,0)	

ELSE 0 END) AS '销售收入',	

SUM(

  CASE WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN ISNULL(a.AmountOfMoney,0) 

	   WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN ISNULL(a.AmountOfMoney,0) 

ELSE 0 END) AS '佣金',	

SUM(

  CASE WHEN a.BillType = '放款' THEN 0	

	   WHEN a.BillType = '退款' THEN 0

	   WHEN a.BillType = '垫付追偿' THEN 0

	   WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN 0

	   WHEN a.BillType = '分账' AND a.TranType LIKE '%联盟佣金' THEN 0

	   WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN 0

	   WHEN a.BillType = '分账退款' AND a.TranType LIKE '%联盟佣金' THEN 0   

ELSE ISNULL(a.AmountOfMoney,0) END) AS '平台其他费用',

0 AS '平台运费',	

0 AS '充值广告费',

SUM(CASE WHEN a.BillType = '分账' AND a.TranType LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)  

		 WHEN a.BillType = '分账退款' AND a.TranType LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)

ELSE 0 END) AS '广告费',

0 AS '财务费用',

'非托管' AS '托管类型'

FROM RB_Crawler_Aliexpress_RussianSettlement a WITH(NOLOCK)

WHERE (a.BillType !='汇兑' AND a.BillType !='换汇结算' AND a.BillType !='换汇结算退款')

AND a.BillDate >= @startdate

AND a.BillDate <= @enddate

--AND a.BusinessOrder='3031820361665770'

-- AND {{ a.OrderSourceID IN (@OrderSourceID) }}

GROUP BY a.BusinessOrder,a.OrderSourceID,a.OrderSourceName,a.Currency,

ISNULL((

 CASE WHEN a.BillType = '放款' THEN '订单收入'  

	  WHEN a.BillType = '退款' THEN '订单退款'

	  WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN '订单收入' 		  

	  WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN '订单退款'	  

	  WHEN a.BillType = '垫付追偿' THEN '订单退款'	  

	  ELSE '非订单' END),'')
 '''
 collect = '''
      -- 俄罗斯的应收明细
     SELECT
     	'俄罗斯' AS '速卖通区域',
     	OrderSourceID as '店铺ID',
       BusinessOrder as '平台订单号',
      	Currency AS '币种',
     	'订单收入' AS '单据类型',
     	ROUND(SUM (
     		(
     		CASE

     				WHEN TranType IN ( '交易物流费','交易商品','交易货款') THEN
     				ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     			else 0 END
     			)
     		),2) AS '销售收入',
     		ROUND(SUM (
     			(
     			CASE

     					WHEN TranType IN ( '交易物流平台佣金', '交易货款平台佣金' ) THEN
     					ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     				else 0 END
     				)
     			),2) AS '佣金',
     			0 AS '平台其他费用',
     				0 as '平台运费',
     				0 as '充值广告费',
     				0 as '广告费',
     				0 as '财务费用',
						'非托管' AS '托管类型'
     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
                 where id in
     			(select MIN(Id)
     			FROM
     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
     			WHERE
     				__CrawlConfigId = '531397896499269'
     				AND TIME >= '2024-04-01 00:00:00'
     				AND TIME <= '2024-04-30 23:59:59'
     				AND type IN ( '分账', '放款' )
 						-- and OrderSourceID in ('20481','12027')
     			GROUP BY RowMd5)
     			GROUP BY
     				OrderSourceID,BusinessOrder,Currency
     				UNION ALL
     			SELECT
     				'俄罗斯' AS '速卖通区域',
     				OrderSourceID as '店铺ID',
       	BusinessOrder as '平台订单号',
     				Currency AS '币种',
     				'非订单' AS '单据类型',
     				0 AS '销售收入',
     				0 AS '佣金',
     				ROUND(SUM (
     					(
     					CASE

     							WHEN TranType IN ( '垫资追缴', '退款物流垫资', '退款使用垫资','交易货款','交易物流费','违背发货承诺订单扣罚') THEN
     							ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     						else 0 END
     						)
     					),2) AS '平台其他费用' ,
     				0 as '平台运费',
     				ROUND(SUM (
     					(
     					CASE

     							WHEN Remarks  like 'AEP4P回款金充值%' THEN
     							ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     						else 0 END
     						)
     					),2) as '充值广告费',
     				ROUND(SUM (
     				(
     				CASE

     						WHEN TranType IN ( '交易货款联盟佣金' ) THEN
     						ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     					else 0 END
     					)
     				),2) as '广告费',
     				0 as '财务费用',
						'非托管' AS '托管类型'
     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
                 where id in
     			(select MIN(Id)
     			FROM
     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
     			WHERE
     					__CrawlConfigId = '531397896499269'
     					AND TIME >= '2024-04-01 00:00:00'
     					AND TIME <= '2024-04-30 23:59:59'
     					AND type IN ( '垫资追缴', '垫付','分账','分账退款' ,'换汇结算','拒付追缴','扣款')
 							-- and OrderSourceID in ('20481','12027')
     			GROUP BY RowMd5)
     			GROUP BY
     				OrderSourceID,BusinessOrder,Currency
     UNION ALL
     				SELECT
     					'俄罗斯' AS '速卖通区域',
     					OrderSourceID as '店铺ID',
              BusinessOrder as '平台订单号',
     				 	Currency AS '币种',
     					'订单退款' AS '单据类型',
     					ROUND(SUM (
     						(
     						CASE

     								WHEN TranType IN ( '交易物流费','交易商品','交易货款' ) THEN
     								ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     							else 0 END
     							)
     						),2) AS '销售收入',
     						ROUND(SUM (
     							(
     							CASE

     									WHEN TranType IN ( '交易物流平台佣金', '交易货款平台佣金' ) THEN
     									ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
     								else 0 END
     								)
     							),2) AS '佣金',
     							0 AS '平台其他费用' ,
     												0 as '平台运费',
     				      0 as '充值广告费',
     			      	0 as '广告费',
     					0 as '财务费用',
							'非托管' AS '托管类型'
     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
                 where id in
     			(select MIN(Id)
     			FROM
     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240501] (nolock)
     			WHERE
                      __CrawlConfigId = '531397896499269'
     				AND TIME >= '2024-04-01 00:00:00'
     				AND TIME <= '2024-04-30 23:59:59'
     				AND type IN ( '分账退款', '退款', '垫付追偿' )
 						-- and OrderSourceID in ('20481','12027')
     			GROUP BY RowMd5)
     			GROUP BY
     				OrderSourceID,BusinessOrder,Currency
     	 union all
     	 -- 国际支付宝的应收明细
     SELECT '国际支付宝' AS '速卖通区域',  OrderSourceID as '店铺ID',
     	merchant_order_number	  as '平台订单号',currency AS '币种','订单收入' AS '单据类型',
      ROUND(SUM (
     						(
     						CASE

     								WHEN remarks like '放款%' THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '销售收入',
     	 ROUND(SUM (
     						(
     						CASE

     								WHEN remarks like '平台佣金扣佣%' THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '佣金',
     			     0 AS '平台其他费用',
     								0 AS '平台运费',
     						0 as '充值广告费',
     						0 AS '广告费',
     						0 as '财务费用',
								'非托管' AS '托管类型'
                 FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
                 where id in
     			(select MIN(Id)
     			FROM
     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
     			WHERE
                          bill_date >= '2024-04-01 00:00:00'
     					AND bill_date <= '2024-04-30 23:59:59'
     					AND __CrawlConfigId = '531397895802949'
     					AND bill_type in ('收款','扣款','分账')
 							-- and OrderSourceID in ('20481','12027')
     			GROUP BY RowMd5)
                 GROUP BY
     			OrderSourceID,merchant_order_number,currency
     	UNION ALL

     SELECT '国际支付宝' AS '速卖通区域', OrderSourceID as '店铺ID',
     		merchant_order_number	  as '平台订单号', currency AS '币种','订单退款' AS '单据类型',
      ROUND(SUM (
     						(
     						CASE

     								WHEN  remarks like '退款	trade order refund%'
     								or remarks like '退款	For quality or delivery issue%'
     								or remarks like 'For quality or delivery issue%'
     								or remarks like 'For quality or deliv%'
     								or remarks like 'trade order%'  THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '销售收入',
     	 ROUND(SUM (
     						(
     						CASE

     								WHEN remarks like '佣金退回%' or remarks like '平台佣金退回%'  THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '佣金',
     			      0 AS '平台其他费用',
     						0	AS '平台运费',
     						0 as '充值广告费',
     						0 AS '广告费',
     						0 as '财务费用',
								'非托管' AS '托管类型'
       FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
                 where id in
     			(select MIN(Id)
     			FROM
     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
     			 WHERE  bill_date >= '2024-04-01 00:00:00'
     			AND bill_date <= '2024-04-30 23:59:59'
     			AND __CrawlConfigId = '531397895802949'
     			AND bill_type in ('扣款','分账退款','退款','收款')
 					-- and OrderSourceID in ('20481','12027')
     			GROUP BY RowMd5)
                 GROUP BY
     			OrderSourceID,merchant_order_number,currency
                union all
     SELECT '国际支付宝' AS '速卖通区域', OrderSourceID as '店铺ID',
    (case when merchant_order_number!='' and LEN(merchant_order_number)<=20  then merchant_order_number
 		 when merchant_order_number='' and LogisticsOrderNo!='AE线上发货' and LEN(LogisticsOrderNo)<=20 then LogisticsOrderNo
 		 else '' end)   as '平台订单号', currency AS '币种','非订单' AS '单据类型',
     0  AS '销售收入',
     	0 AS '佣金',
     			 ROUND(SUM (
     						(
     						CASE
     								WHEN remarks like '菜鸟代发	代发_服务赔付%'
     								or remarks like '菜鸟代发	代发_退出口目的国关税%'
     								or remarks like '菜鸟代发	代发_货值赔付%'
     								or remarks like '菜鸟代发	代发_配送附加费%'
     								or remarks like 'DDP关税结算%'
     								or remarks like 'DDP关税退款%'
     								or remarks like '退还EPR费用%'
     								or remarks like '菜鸟物流费代扣	代扣_出口目的国关税%'
     								or remarks like '菜鸟物流费代扣	代扣_目的国进口税金%'
     								or remarks like '菜鸟物流费代扣	代扣_冲货处理附加费%'
     								or remarks like '菜鸟物流费代扣	代扣_配送附加费%'
     								or remarks like '菜鸟物流费代扣	代扣_超时发货费%'
     								or remarks like '菜鸟物流费代扣	代扣_退货服务费%'
     								or remarks like '菜鸟物流费代扣	代扣_退货仓内处理费%'
     								or remarks like '菜鸟物流费代扣	代扣_逆向退货配送费%'
     								or remarks like '买家退货运费%'
     								or remarks like 'AE_EU_ENVIRONMENT%'
     								or remarks like '限时达不达标处罚%'
     								or remarks like '4PL_PLATFORM_RECOVER%'
     								or remarks like 'EPR费用%'
     								or remarks like 'POP_SELLER_DELIVER_PUNISH_%'
     								or remarks like  '违背发货承诺订单扣罚%'
     								or remarks like '菜鸟代发	代发_美容健康%'
     								or  remarks like '赔付%'
     								or remarks like '垫资还款冻结%'
     					    	or remarks like '退款垫付%'
     								or remarks like '环保费%'
     								or remarks like '中东地区满包邮活动服务费%'
     								or remarks like '垫资/欠费还款的解冻并扣款%'
                                     or remarks like '菜鸟物流费代扣	代扣_代付税金%'
     								or remarks like '菜鸟物流费代扣	代扣_代付巴西%'
     								or remarks like '巴西%'
     								or remarks like '菜鸟物流费代扣	代扣_消退入库操作费%'
     								or remarks like '本地退买家退货%'
     								or remarks like '菜鸟物流费代扣	代扣_代付巴西ICMS税%'
     								or remarks like '本地退退货运费%'
 								    or remarks like '本地退退货质检费%'
     								or  remarks like '3PL_REFUND_COMPENSATE_ITEM%'
     								or remarks like '扣款	扣款用于偿还垫资欠款%'
     								or remarks like '半托管JIT超时处罚%'
                                     or remarks like '半托管买家退货代扣退货质检费%'
                                     or remarks like '半托管买家退货代扣运费%'
                                     or  remarks like '本地退退货操作费%'
                                      or TranType='HALFSTOPTRADESTATETA'
                                       or TranType='物流上网超时处罚'
                                        or TranType='半托管物流费赔付' 
                                         or TranType='极速退非商家责平台赔付商家'
                                      or TranType in ('DDP税退款','DDP税放款','半托管本地退退货操作费',
                                      '半托管物流原因退款赔付半托管物流原因退款','菜鸟物流费代扣代扣免检销毁费',
                                       'HALFSTOPINCONSISWEIG','DDP税代缴','HALFSTOPTRADEDUTYTAX')
                                      THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '平台其他费用',
     						ROUND(SUM (
     						(
     						CASE
     								WHEN remarks like '菜鸟物流费代扣	代扣_正向配送费%'  or remarks like '菜鸟代发	代发_正向配送费%' or remarks like '半托管发货物流费%'  THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '平台运费',
     						 ROUND(SUM (
     						(
     						CASE
     								WHEN remarks like '直通车充值%'  or remarks like 'AEP4P回款金充值%'  THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '充值广告费',
     						ROUND(SUM (
     						(
     						CASE
     								WHEN remarks like '联盟佣金扣佣%' or remarks like '联盟佣金退回%' THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2) AS '广告费',
     						 ROUND(SUM (
     						(
     						CASE
     								WHEN  TranType='服务费' or remarks like '自动转账%'  THEN
     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
     							else 0 END
     							)
     						),2)  as '财务费用',
								'非托管' AS '托管类型'

     			FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
                 where id in
     			(select MIN(Id)
     			FROM
     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240501] ( nolock )
     	       WHERE bill_date >= '2024-04-01 00:00:00'
                AND bill_date <= '2024-04-30 23:59:59'
                AND __CrawlConfigId = '531397895802949'
 							-- and OrderSourceID in ('20481','12027')
                AND bill_type in ('收款','退款','扣款','服务费','分账','分账退款','垫付')
     			GROUP BY RowMd5)
                 GROUP BY
     			OrderSourceID, case when merchant_order_number!='' and LEN(merchant_order_number)<=20  then merchant_order_number
 		 when merchant_order_number='' and LogisticsOrderNo!='AE线上发货' and LEN(LogisticsOrderNo)<=20 then LogisticsOrderNo
 		 else '' end  ,currency
		union all
		 SELECT
     	'国际支付宝' AS '速卖通区域',
     	OrderSourceId as '店铺ID',
       MerchantTransactionNo as '平台订单号',
      	Currency AS '币种',
     	'订单收入' AS '单据类型',
     	ROUND(SUM (
     		(
     			case
					 WHEN Remarks like '全托管供货款%'  THEN
     				ISNULL( CAST ( replace( Amount, ',', '' ) AS FLOAT ), 0 )
     			else 0 END
     			)
     		),2) AS '销售收入',
     		0 AS '佣金',
     			0 AS '平台其他费用',
     				0 as '平台运费',
     				0 as '充值广告费',
     				0 as '广告费',
     				0 as '财务费用',
						'全托' AS '托管类型'
     			from [AliExpressPlatformPRD].[dbo].[AliExpressFullBillHistory_20240501] (nolock)
                 where id in
     			(select MIN(Id)
     			FROM
     				[AliExpressPlatformPRD].[dbo].[AliExpressFullBillHistory_20240501] (nolock)
     			WHERE
     				__CrawlConfigId='531397899800645'
						and  __CreateDateTime>='2024-05-09'
						and Type IN ( '收款','扣款')
     			GROUP BY RowMd5)
     			GROUP BY
     				OrderSourceID,MerchantTransactionNo,Currency
			union all
				SELECT
     	'国际支付宝' AS '速卖通区域',
     	OrderSourceId as '店铺ID',
       MerchantTransactionNo as '平台订单号',
      	Currency AS '币种',
     	'非订单' AS '单据类型',
        0 AS '销售收入',
     		0 AS '佣金',
     				ROUND(SUM (
     		(
					CASE
     				WHEN Remarks like '货款追缴%'
						or  Remarks like 'ONE_STO%'
						OR Remarks like 'JIT入库订单%'
						or remarks like '订单货款追回%'
						or remarks like '订单退货罚款%' THEN
     				ISNULL( CAST ( replace( Amount, ',', '' ) AS FLOAT ), 0 )
     			else 0 END
     			)
     		),2) AS '平台其他费用',
     				0 as '平台运费',
     				0 as '充值广告费',
     				0 as '广告费',
     				0 as '财务费用',
						'全托' AS '托管类型'
     			from [AliExpressPlatformPRD].[dbo].[AliExpressFullBillHistory_20240501] (nolock)
                 where id in
     			(select MIN(Id)
     			FROM
     				[AliExpressPlatformPRD].[dbo].[AliExpressFullBillHistory_20240501] (nolock)
     			WHERE
     				__CrawlConfigId='531397899800645'
						and  __CreateDateTime>='2024-05-09'
						and Type='扣款'
     			GROUP BY RowMd5)
     			GROUP BY
     				OrderSourceID,MerchantTransactionNo,Currency;
      '''
 database={'erp测试库':'sqlserver|10.158.158.48|1433|sa|zhcx@2021#PD|irobotbox','采集':'sqlserver|116.205.173.204|1433|datareadonly|5FP%NijFUx@B&TFt|AliExpressPlatformPRD',
               'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox'}
 datacomp=DataComparison(database)
 value=('销售收入', '佣金', '平台其他费用', '平台运费', '充值广告费', '广告费','财务费用')
 keys=('速卖通区域','平台订单号','店铺ID','币种','单据类型')
 erp_data=datacomp.data_organization('erp测试库',erp,keys,value)
 collect_data=datacomp.data_organization('采集',collect,keys,value)
 # erp_data=datacomp.data_organization('erp测试库',sql_data['erp'],keys,value)
 # collect_data=datacomp.data_organization('采集',sql_data['collect'],keys,value)
 datacomp.data_compare(collect_data,erp_data,value)